package screens;

import com.alee.laf.panel.WebPanel;
import com.googlecode.charts4j.*;
import sql.Db;
import sql.ResultSetHandler;
import util.FontLoader;
import view.ChartView;
import view.StatView;

import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.JComponent;
import javax.swing.JLabel;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridLayout;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class StatsScreen extends WebPanel {

    /**
     * Shows a list of statistics about users/posts/comments in the system.
     */
    public StatsScreen() {
        this.setLayout(new BoxLayout(this, BoxLayout.PAGE_AXIS));
        this.setOpaque(false);

        WebPanel stats = new WebPanel();
        stats.setOpaque(false);
        stats.setLayout(new GridLayout(1, 0, 10, 10));

        getCounts(stats);
        add(stats);

        add(Box.createRigidArea(new Dimension(0, 50)));
        getPostsByHour();
        add(Box.createRigidArea(new Dimension(0, 50)));
        getMap();
        add(Box.createRigidArea(new Dimension(0, 50)));

        WebPanel pi = new WebPanel();
        pi.setOpaque(false);
        pi.setLayout(new GridLayout(1, 0, 0, 0));
        add(pi);

        getUsersGender(pi);
        getTopCities(pi);

        add(Box.createRigidArea(new Dimension(0, 50)));

    }

    /**
     * Get interesting counts (posts, users, comments)
     * @param parent Parent to add chart to
     */
    public void getCounts(final JComponent parent) {
        Db db = Db.getDb();
        db.executeQuery("SELECT a.posts, b.users, c.comments\n" +
            "FROM\n" +
            "(\n" +
                "SELECT COUNT(*) as posts\n" +
                "FROM post\n" +
            ") a,\n" +
            "(\n" +
                "SELECT COUNT(*) as users\n" +
                "FROM user\n" +
            ") b,\n" +
            "(\n" +
                "SELECT COUNT(*) as comments\n" +
                "FROM comment\n" +
            ") c;",

            new ResultSetHandler() {
                @Override
                public void handleResult(ResultSet rs) {
                    try {
                        rs.next();
                        parent.add(new StatView("Users", rs.getInt("users")));
                        parent.add(new StatView("Posts", rs.getInt("posts")));
                        parent.add(new StatView("Comments", rs.getInt("comments")));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        );
    }

    /**
     * Get the cities with the most users
     * @param parent Parent to add chart to
     */
    public void getTopCities(final JComponent parent) {
        Db db = Db.getDb();
        db.executeQuery(
            "SELECT COUNT(city) as Count, city\n" +
            "FROM user\n" +
            "WHERE LENGTH(state) > 0\n" +
            "GROUP BY city\n" +
            "ORDER BY Count DESC\n" +
            "LIMIT 5",

            new ResultSetHandler() {
                public void handleResult(ResultSet rs) {
                    ArrayList<String> cities = new ArrayList<String>();
                    ArrayList<Integer> counts = new ArrayList<Integer>();
                    int count = 0;

                    try {
                        while (rs.next()) {
                            String city = rs.getString(2);
                            if (city.length() == 0) {
                                city = "Unknown";
                            } else {
                                char[] stringArray = city.toCharArray();
                                stringArray[0] = Character.toUpperCase(stringArray[0]);
                                city = new String(stringArray);
                            }
                            cities.add(city);
                            counts.add(rs.getInt(1));
                            count += rs.getInt(1);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    ArrayList<Slice> slices = new ArrayList<Slice>();
                    Color[] colors = new Color[] { Color.newColor("5676C9"), Color.newColor("5890FB"), Color.newColor("BC3C45"), Color.newColor("FC9E00"), Color.newColor("D4E131")};

                    for (int i = 0; i < cities.size(); i += 1) {
                        slices.add(Slice.newSlice(Math.round(((float)counts.get(i) / (float)count) * 100), colors[i], cities.get(i) + ": " + counts.get(i)));
                    }

                    final PieChart chart = GCharts.newPieChart(slices);
                    chart.setOrientation(1.5 * Math.PI);

                    chart.setSize(300, 150);
                    ChartView cv = new ChartView("Top 5 Cities");
                    cv.showChart(chart, new Dimension(300, 200));
                    parent.add(cv);
                }
            }
        );
    }

    private USAState.Name stringToState(String name) {
        try {
            return USAState.Name.valueOf(name.toUpperCase().replaceAll(" ", "_"));
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * Get a map of posts by state
     */
    public void getMap() {
        JLabel v = new JLabel("Posts by State");
        Font f = FontLoader.getFont(FontLoader.OPENSANS_BOLD);
        f = f.deriveFont(26f);
        v.setAlignmentX(Component.CENTER_ALIGNMENT);
        v.setForeground(java.awt.Color.DARK_GRAY);
        v.setFont(f);
        add(v);

        Db db = Db.getDb();
        db.executeQuery(
            "SELECT COUNT(state) as Count, state\n" +
                    "FROM user, post\n" +
                    "WHERE user.id = post.authorID\n" +
                    "GROUP BY state",

            new ResultSetHandler() {
                public void handleResult(ResultSet rs) {
                    ArrayList<Integer> counts = new ArrayList<Integer>();
                    ArrayList<String> states = new ArrayList<String>();
                    int count = 0;

                    try {
                        while (rs.next()) {
                            count += rs.getInt(1);
                            counts.add(rs.getInt(1));
                            states.add(rs.getString(2));
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    ArrayList<PoliticalBoundary> boundaries = new ArrayList<PoliticalBoundary>();

                    for (int i = 0; i < counts.size(); i++) {
                        USAState.Name name = stringToState(states.get(i));
                        if (name != null) {
                            boundaries.add(new USAState(name, Math.min(100, Math.round((float) counts.get(i) / (float) count * 1200))));
                        }
                    }

                    Line line1 = Plots.newLine(DataUtil.scaleWithinRange(0, 1000, counts), Color.newColor("15BBE6"));
                    line1.setLineStyle(LineStyle.newLineStyle(3, 1, 0));
                    line1.addShapeMarkers(Shape.CIRCLE, Color.newColor("15BBE6"), 12);
                    line1.addShapeMarkers(Shape.CIRCLE, Color.WHITE, 8);
                    line1.setFillAreaColor(Color.newColor("15BBE6", 30));

                    final MapChart chart = GCharts.newMapChart(GeographicalArea.USA);

                    chart.addPoliticalBoundaries(boundaries);
                    chart.setColorGradient(Color.WHITE, Color.LIGHTGREEN, Color.GREEN);

                    ChartView cv = new ChartView();
                    add(cv);
                    cv.showChart(chart, new Dimension(440, 220));
                }
            }
        );
    }



    /**
     * Get posts grouped by hour
     */
    public void getPostsByHour() {
        JLabel v = new JLabel("Posts by Hour");
        Font f = FontLoader.getFont(FontLoader.OPENSANS_BOLD);
        f = f.deriveFont(26f);
        v.setAlignmentX(Component.CENTER_ALIGNMENT);
        v.setForeground(java.awt.Color.DARK_GRAY);
        v.setFont(f);
        add(v);

        Db db = Db.getDb();
        db.executeQuery(
            "SELECT COUNT(*) as Count, HOUR(CONVERT_TZ(timestamp,'+00:00', '-08:00')) as Hour\n" +
                    "FROM post\n" +
                    "GROUP BY HOUR(CONVERT_TZ(timestamp,'+00:00', '-08:00'));",

            new ResultSetHandler() {
                public void handleResult(ResultSet rs) {
                    ArrayList<Integer> counts = new ArrayList<Integer>();

                    try {
                        while (rs.next()) {
                            counts.add(rs.getInt(1));
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    Line line1 = Plots.newLine(DataUtil.scaleWithinRange(0, 1000, counts), Color.newColor("15BBE6"));
                    line1.setLineStyle(LineStyle.newLineStyle(3, 1, 0));
                    line1.addShapeMarkers(Shape.CIRCLE, Color.newColor("15BBE6"), 12);
                    line1.addShapeMarkers(Shape.CIRCLE, Color.WHITE, 8);
                    line1.setFillAreaColor(Color.newColor("15BBE6", 30));

                    final LineChart chart = GCharts.newLineChart(line1);

                    AxisLabels yAxis = AxisLabelsFactory.newAxisLabels("0", "200", "400", "600", "800", "900");
                    List<String> labels = Arrays.asList("4:00am", "8:00am", "12:00pm", "4:00pm", "8:00pm");
                    List<Double> positions = Arrays.asList(17.3913043, 34.7826087,52.173913,  69.5652174, 86.9565217);
                    chart.addXAxisLabels(AxisLabelsFactory.newAxisLabels(labels, positions));
                    chart.addYAxisLabels(yAxis);
                    chart.setGrid(17.3913043, 20, 5, 5);

                    chart.setSize(600, 250);
                    ChartView cv = new ChartView();
                    cv.showChart(chart, new Dimension(600, 250));
                    add(cv);
                }
            }
        );
    }

    /**
     * Get users by gender
     * @param parent Parent to add chart to
     */
    public void getUsersGender(final JComponent parent) {
        Db db = Db.getDb();
        db.executeQuery(
            "SELECT sex, COUNT(sex) as \"Count\"\n" +
                    "FROM user\n" +
                    "GROUP BY sex;",

            new ResultSetHandler() {
                public void handleResult(ResultSet rs) {
                    ArrayList<String> genders = new ArrayList<String>();
                    ArrayList<Integer> counts = new ArrayList<Integer>();
                    int count = 0;

                    try {
                        while (rs.next()) {
                            String gender = rs.getString(1);
                            if (gender.length() == 0) {
                                gender = "Unknown";
                            } else {
                                char[] stringArray = gender.toCharArray();
                                stringArray[0] = Character.toUpperCase(stringArray[0]);
                                gender = new String(stringArray);
                            }
                            genders.add(gender);
                            counts.add(rs.getInt(2));
                            count += rs.getInt(2);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    ArrayList<Slice> slices = new ArrayList<Slice>();
                    Color[] colors = new Color[] { Color.newColor("C63D46"), Color.newColor("FC9E00"), Color.newColor("5890FB")};

                    for (int i = 0; i < genders.size(); i += 1) {
                        slices.add(Slice.newSlice(Math.round(((float)counts.get(i) / (float)count) * 100), colors[i], genders.get(i) + ": " + counts.get(i)));
                    }

                    final PieChart chart = GCharts.newPieChart(slices);

                    chart.setSize(300, 150);
                    ChartView cv = new ChartView("Users by Gender");
                    cv.showChart(chart, new Dimension(300, 200));
                    parent.add(cv);
                }
            }
        );
    }
}
